from django.db import models
from django.db.models import Q

class CategoryManager(models.Manager):
    ''' Category Manager '''
    def with_tasks_count(self):
        ''' return list of categories with info about total, open and closed tasks ''' 
        from django.db import connection
        cursor = connection.cursor()
        cursor.execute('''
            SELECT c.id as id, c.code as code, c.description as description,
            (SELECT count(*) FROM tasktracker_task WHERE category_id = c.id) as total_tasks,
            (SELECT count(*) FROM tasktracker_task WHERE category_id = c.id AND status IN (1,2)) as open_tasks,
            (SELECT count(*) FROM tasktracker_task WHERE category_id = c.id AND status IN (3,4,5)) as closed_tasks
            FROM tasktracker_category as c''')
        result_list = []
        for row in cursor.fetchall():
            p = self.model(id=row[0], code=row[1], description=row[2])
            p.total_tasks = row[3]
            p.open_tasks = row[4]
            p.closed_tasks = row[5]
            result_list.append(p)
        return result_list

class TaskManager(models.Manager):
    ''' Task manager '''
    def open(self):
        ''' select open tasks (with status: Queued, Processing) '''
        return self.filter(Q(status__exact = 1) | Q(status__exact = 2))

    def closed(self):
        ''' select closed tasks (with status: Completed, Cancelled, Failed) '''
        return self.filter(Q(status__exact = 3) | Q(status__exact = 4) | Q(status__exact = 5))
        